package com.rmb.service.impl;

import com.alibaba.fastjson.JSON;
import com.rmb.client.DataAnalysisClient;
import com.rmb.common.exception.ExceptionCast;
import com.rmb.common.model.assist.AssistQuery;
import com.rmb.common.model.response.CommonCode;
import com.rmb.common.model.response.ResponseResult;
import com.rmb.common.model.sensitive.Desensitization;
import com.rmb.common.model.sensitive.IdDesensitizationUtil;
import com.rmb.common.model.state.TaskStatus;
import com.rmb.dao.SourceDao;
import com.rmb.entity.ResponeRest;
import com.rmb.entity.SourceEntity;
import com.rmb.entity.result.AddDataSourceResult;
import com.rmb.mapper.SourceMapper;
import com.rmb.pool.RedisConnention;
import com.rmb.service.SourceService;
import com.rmb.util.DesUtil;
import com.rmb.util.JdbcConnector;
import com.rmb.util.JdbcUtil;
import com.rmb.util.RedisConnector;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.test.annotation.Rollback;
import org.springframework.transaction.annotation.Transactional;
import redis.clients.jedis.Jedis;

import java.sql.*;
import java.util.*;

/**
 * @author ZhaoMyYang
 */
@Service
@Transactional(rollbackFor = Exception.class)
public class SourseServiceImpl implements SourceService {
    private static final String MYSQL = "MySql";
    private static final String REDIS = "redis";
    private static final Integer MAXPAGESIZE = 50;
    private static final Integer MINPAGESIZE = 10;

    @Autowired(required = false)
    private SourceMapper sourceMapper;

    @Autowired
    private SourceDao sourceDao;

    @Autowired
    private DataAnalysisClient dataAnalysisClient;

    /**
     * 查询列表
     *
     * @return
     */
    @Override
    public List<SourceEntity> findList() {
        return sourceDao.findAll();
    }

    /**
     * 获取请求数据
     *
     * @param id
     * @return
     */
    @Override
    public AddDataSourceResult findById(Long id) {
        SourceEntity sourceEntity = sourceMapper.findById(id);
        if (sourceEntity == null) {
            ExceptionCast.cast(CommonCode.THIS_OBJECT_NULL);
        }
        //转为json字符串
        String source = JSON.toJSONString(sourceEntity);
        //加密数据
        String encrypt = DesUtil.encrypt(source.getBytes(), "linxi_lst");
        return new AddDataSourceResult(CommonCode.SUCCESS, encrypt);
    }

    /**
     * 修改数据源
     *
     * @param sourceEntity 实体类
     * @param id           主键
     * @return 结果集
     */
    @Override
    public ResponseResult updateDataSource(SourceEntity sourceEntity, Long id) {
        SourceEntity mapper = sourceMapper.findById(id);
        //数据正在运行中，提示不能修改
        if (mapper != null && mapper.getUsestate() == 0) {
            ExceptionCast.cast(CommonCode.RUNNING_PLEASE_CLOSE);
        }
        //对象为空
        if (sourceEntity == null) {
            ExceptionCast.cast(CommonCode.THIS_OBJECT_NULL);
        }
        sourceEntity.setId(id);

        //修改成功
        boolean b = sourceMapper.updateDataSource(sourceEntity);
        if (!b) {
            return new ResponseResult(CommonCode.FAIL);
        }

        return new ResponseResult(CommonCode.SUCCESS);
    }


    @Override
    public SourceEntity addSource(SourceEntity sourceEntity) {
        if (sourceEntity == null) {
            ExceptionCast.cast(CommonCode.THIS_OBJECT_NULL);
        }
        //确认类型
        if (MYSQL.equals(sourceEntity.getSourceType())) {
            return this.addJdbc(sourceEntity);
        } else if (REDIS.equals(sourceEntity.getSourceType())) {
            return this.addRedis(sourceEntity);
        }
        return null;
    }


    @Override
    public void delte(Long id) {

        Optional<SourceEntity> byId = sourceDao.findById(id);
        SourceEntity sourceEntity = byId.get();

        sourceEntity.setState(0);

        sourceDao.save(sourceEntity);


    }


    /**
     * 测试并添加
     *
     * @param sourceEntity
     * @return
     */
    private SourceEntity addJdbc(SourceEntity sourceEntity) {
        Boolean getconnection = JdbcUtil.getConnection(sourceEntity);
        if (!getconnection) {
            ExceptionCast.cast(CommonCode.FAIL);
        }
        SourceEntity save = sourceDao.save(sourceEntity);
        JdbcConnector.put(sourceEntity);
        return save;
    }

    /**
     * 测试并添加
     *
     * @param sourceEntity
     * @return
     */
    private SourceEntity addRedis(SourceEntity sourceEntity) {
        Jedis redisConnection = RedisConnention.initPool(sourceEntity);

        if (redisConnection == null) {
            ExceptionCast.cast(CommonCode.FAIL);
        }

        String s = redisConnection.get("sys_config:sys.index.sideTheme");
        System.out.println("redis连接成功-------》》》》》" + redisConnection);
        System.out.println(s);
        SourceEntity save = sourceDao.save(sourceEntity);
        RedisConnector.getRedisConnection(sourceEntity);

        return save;
    }

    /**
     * 测试
     *
     * @param id
     * @return
     */
    @Override
    public ResponseResult testHeartbeat(Long id) {
        Optional<SourceEntity> optional = sourceDao.findById(id);
        if (!optional.isPresent()) {
            return new ResponseResult(CommonCode.FAIL);
        }

        SourceEntity sourceEntity = optional.get();
        //判断数据类型
        if (MYSQL.equals(sourceEntity.getSourceType())) {
            Boolean getconnection = JdbcConnector.isConnected(sourceEntity);
            if (getconnection) {
                return new ResponseResult(CommonCode.SUCCESS);
            } else {
                return new ResponseResult(CommonCode.FAIL);
            }
        } else {
            Jedis redisConnection = RedisConnention.initPool(sourceEntity);

            if (redisConnection == null) {
                return new ResponseResult(CommonCode.FAIL);
            }
            System.out.println("redis连接成功-------》》》》》" + redisConnection);
            String s = redisConnection.get("sys_config:sys.index.sideTheme");
            System.out.println(s);
            return new ResponseResult(CommonCode.SUCCESS);
        }
    }

    /**
     * 根据ID回显
     * @param id
     * @param flag
     * @return
     */
    @Override
    public ResponeRest findSourceById(Long id, String flag) {
        //id为空抛出异常
        if (id == null) {
            ExceptionCast.cast(CommonCode.THIS_ID_NULL);
        }
        //查询不到数据抛出异常
        SourceEntity sourceById = sourceDao.findById(id).get();
        if (sourceById == null) {
            ExceptionCast.cast(CommonCode.THIS_OBJECT_NULL);
        }
        //判断是否要加密
        if (TaskStatus.EXECUTOR_COTACT_STATUS.getStatus().equals(flag)) {
            //转为json字符串,加密
            String password = DesUtil.encrypt(JSON.toJSONString(sourceById).getBytes(), "password");
            return new ResponeRest(true, password);
        }
        return new ResponeRest(true, sourceById);
    }

    /**
     * 修改数据源
     *
     * @param sourceEntity
     */
    @Override
    public void update(SourceEntity sourceEntity) {
        sourceDao.saveAndFlush(sourceEntity);
    }

    @Override
    public List<SourceEntity> list() {
        return sourceDao.findAll();
    }


    /**
     * 分页展示，最少10条最多50
     * 加密
     * @param pageSize
     * @param pageNum
     * @return
     */
    @Override
    public String findAlldes(Integer pageSize, Integer pageNum) {
        //判断查询数据是否超过50条
        if (pageNum > MAXPAGESIZE) {
            pageNum = MAXPAGESIZE;
            System.out.println("查询数据过多");
        }
        List pageAll = sourceMapper.findPageAll(pageSize, pageNum);
        String jsonString = JSON.toJSONString(pageAll);
        String encrypt = DesUtil.encrypt(jsonString.getBytes(), "zeng_ll_csdn");
        return encrypt;
    }

    /**
     * 分页展示，最少10条最多50
     * 不加密
     *
     * @param pageSize
     * @param pageNum
     * @return
     */
    @Override
    public String findPageAll(Integer pageSize, Integer pageNum) {
        //判断查询数据是否超过50条
        if (pageNum > MAXPAGESIZE) {
            pageNum = MAXPAGESIZE;
            System.out.println("查询数据过多");
        }
        List pageAll = sourceMapper.findPageAll(pageSize, pageNum);
        String jsonString = JSON.toJSONString(pageAll);
        return jsonString;
    }

    @Override
    public List findAll() {
        return sourceDao.findAll();
    }

    @Override
    public SourceEntity getsql(Long id) {
        return sourceDao.findById(id).get();
    }

    @Override
    public List<Map<String,Object>> findAllDataSource() {
        return sourceMapper.findAllDataSource();
    }

    @Override
    public List<String> findAllDataBase(Long dataSourceId) {
        List<String> databases = new ArrayList<>();


        Map<Long, Connection> jdbcPoolMap = JdbcConnector.MYJDBC_CONNECTION_MAP;
        Set<Long> longs = jdbcPoolMap.keySet();
        for (Long aLong : longs) {
            Connection connection = jdbcPoolMap.get(aLong);
            try {
                DatabaseMetaData metaData = connection.getMetaData();
                ResultSet rs = metaData.getCatalogs();
                while (rs.next()) {
                    databases.add(rs.getString(TaskStatus.EXECUTOR_COTACT_STATUS.getStatus()));
                }

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            System.out.println(databases);
        }
        return databases;
    }


    @Override
    public List<String> findTablesByDataSource(Long dataSourceId,String dataBaseName) throws SQLException {
        List<String> tables = new ArrayList<>();

        Connection connection = JdbcConnector.MYJDBC_CONNECTION_MAP.get(dataSourceId);

        DatabaseMetaData metaData = connection.getMetaData();

        //获取所有表 库名称，用户名，查询所有，表名称
        ResultSet rsTables = metaData.getTables(
                sourceDao.findById(dataSourceId).get().getDatabaseName(),
                sourceDao.findById(dataSourceId).get().getUsername(),
                AssistQuery.TABLE_CONDITION.getContent(),
                new String[]{AssistQuery.QUERY_OF_TABLE.getContent()});

        while(rsTables.next()){
            tables.add(rsTables.getString(AssistQuery.TABLE_NAME.getContent()));
        }

        return tables;
    }

    @Override
    public SourceEntity getDataSourceById(Long dataSourceId) {
        return sourceDao.findById(dataSourceId).get();
    }


    /**
     * 执行sql查询语句
     * @param dataSourceId 数据源ID
     * @param sql sql语句
     * @return 数据分析
     */
    @Override
    public List<Map<String, Object>> executeSqlQuery(Long dataSourceId,String dataBaseName,String tableName, String sql) {
        List<Map<String, Object>> queryList = new ArrayList<>();

        Connection connection = JdbcConnector.MYJDBC_CONNECTION_MAP.get(dataSourceId);
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            statement = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

            statement.execute("use " + dataBaseName);

            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                //获取表结构
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();

                Map<String, Object> columnMap = new HashMap<>(16);

                for (int i = TaskStatus.EXECUTOR_COTACT_STATUS.getStatus(); i < columnCount + TaskStatus.EXECUTOR_COTACT_STATUS.getStatus(); i++) {
                    //获取列名
                    String colname = metaData.getColumnName(i);

                    //判断是否存在敏感字段
                    if(colname.equals(Desensitization.USER_USERNAME) || colname.equals(Desensitization.USER_PASSWORD) || colname.equals(Desensitization.USER_PHONE) || colname.equals(Desensitization.USER_DEPOSIT)){

                        columnMap.put(colname,IdDesensitizationUtil.replaceStr(resultSet.getString(i)));

                    }else{
                        columnMap.put(colname,resultSet.getString(i));
                    }

                }
                queryList.add(columnMap);
            }
        } catch (Exception exception) {
            queryList=null;
            exception.printStackTrace();
        }finally {
            if(statement!=null){
                try {
                    statement.close();
                    assert resultSet != null;

                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
        assert queryList != null;
        queryList.forEach(System.out::println);

        String jsonString = JSON.toJSONString(queryList);

        dataAnalysisClient.addAnalysis(sql, jsonString,dataSourceId,dataBaseName,tableName);

        return queryList;
    }

    /**
     * 将List<Map<String,Object>>格式转为json字符串格式
     * @param mapList  格式：[{level=1, customItem=a}, {level=2, customItem=a}, {level=3, customItem=a}]
     * @return json字符串
     */
    public String getMapList(List<Map<String,Object>> mapList) {

        Map<String,Object> stringMap = new HashMap<>(16);
        //map的格式：{level=1, customItem=a}
        for (Map<String, Object> map : mapList) {
            //values的格式：[1, a]
            Collection<Object> values = map.values();
            //valueList的格式：[1, a]
            List<Object> valueList = new ArrayList<>(values);
            //添加stringMap，键和值
            stringMap.put(valueList.get(TaskStatus.NOT_IMPLEMENTED_STATUS.getStatus())+"",valueList.get(TaskStatus.EXECUTOR_COTACT_STATUS.getStatus())+"");
        }
        //将stringMap转换成json字符串
        return JSON.toJSONString(stringMap);
    }
}
